/*
 * librolgps, part of the RolGPS Proyect
 *
 *   
 * Copyright (C) Pablo Alejandro Costesich <rhfixer@gmail.com>
 *
 * This library is free software; you can redistribute it and/or
 * modify it under the terms of the GNU Lesser General Public
 * License as published by the Free Software Foundation; either
 * version 2.1 of the License, or (at your option) any later version.
 *
 * This library is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this library; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA
 */
package databaseHandler;

import java.io.File;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author xeph
 */
class sqliteHandler {

    private String driverManager = "org.sqlite.JDBC";
    private String connectString = "jdbc:sqlite:";
    private String sqliteSource = "";
    private Connection database = null;
    /*
     * * * * * Métodos * * * * *
     */

    sqliteHandler(File databaseFile) {
        setSqliteSource(databaseFile.toString());
    }

    sqliteHandler(String databaseFile) {
        setSqliteSource(databaseFile);
    }

    sqliteHandler() {
        setSqliteSource("test.db");
    }

    protected void exportTables(String... tableName) {
    //con un foreach puedo recorrer todos los índices de tableName.
    }

    protected void setDatabaseFile(File sqliteSourceFile) {
        setSqliteSource(sqliteSourceFile.toString());
    }

    protected File getCurrentDatabaseFile() {
        File temp = new File(getSqliteSource());
        return temp;
    }

    protected void exportSql(File dbName) {
    //algún día lo implementaré.

    }

    protected String escapeField(String toScape) {
        //System.err.println("Escapando.");
        return toScape.replaceAll("\'", "\'\'");
    }

    protected String escapeQuery(String query, String[] values) throws SQLException {
        //System.err.println("Escapando.");
        for(int x=0; x<values.length; x++){
            values[x]=escapeField(values[x]);
        }
        int index = 0;
        int arrayIndex=0;
        while((index = query.indexOf("?", index))<query.length() ){
            if(index<0){
                break;
            }else{
                query=query.substring(0, index) + values[arrayIndex] + query.substring(index+values[arrayIndex].length());
                index=index+values[arrayIndex].length();
                arrayIndex++;
            }
        }
        return query;
    }

    protected String getSqliteSource() {
        return sqliteSource;
    }

    protected void setSqliteSource(String sqliteSource) {
        this.sqliteSource = sqliteSource;
    }

    protected String getDriverManager() {
        return driverManager;
    }

    protected void setDriverManager(String driverManager) {
        this.driverManager = driverManager;
    }

    protected String getConnectString() {
        return connectString;
    }

    protected void setConnectString(String connectString) {
        this.connectString = connectString;
    }

    protected String[] getSchema() throws SQLException {
        String sqlStatement = "pragma database_list;";
        Object[][] schemata = this.executeStatement(sqlStatement);
        //descartar el rowid
        String[] result = new String[schemata.length];
        int idx = 0;
        for (Object data[] : schemata) {
            result[idx] = data[1].toString();
            idx++;
        }
        return result;
    }

    protected String[] describeTable(String table) throws SQLException {
        String sqlStatement = "pragma table_info(" + table + ";";
        Object[][] schemata = this.executeStatement(sqlStatement);
        //descartar el rowid
        String[] result = new String[schemata.length];
        int idx = 0;
        for (Object data[] : schemata) {
            result[idx] = data[1].toString();
            idx++;
        }
        return result;
    }

    public String[] getTables() {
        //devuelve las tablas existentes en la base de datos
        return new String[1];
    }

    public void dropTable(String table) {
        //borra una tabla 
        throw new UnsupportedOperationException("Not supported yet.");
    }

    private void openConnection() throws SQLException {
        try {
            Class.forName(driverManager);
            database = DriverManager.getConnection(this.getConnectString() + this.getSqliteSource().toString());
            database.setAutoCommit(false);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(sqliteHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void closeConnection() {
        try {
            if (!database.getAutoCommit()) {
                database.commit();
            }
            database.clearWarnings();
            database.close();
        } catch (SQLException ex) {
            Logger.getLogger(sqliteHandler.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    protected Object[][] executePreparedStatement(String sqlStatement, String[]... values) throws SQLException {
        int colNumber;
        Object[][] resultado = new Object[0][0];

        openConnection();

        PreparedStatement prepStatement = database.prepareStatement(sqlStatement);
        if (sqlStatement.toLowerCase().startsWith("select")) {
            ResultSet selectResult = prepStatement.executeQuery();
            colNumber = selectResult.getMetaData().getColumnCount();
            Vector<String[]> rows = new Vector(); //interfaz String[] para ahorrar pasos.
            String[] columns = new String[colNumber];
            while (selectResult.next()) {
                for (int readIteration = 0; readIteration < colNumber; readIteration++) {
                    if (selectResult.getString(readIteration + 1) == null) {
                        columns[readIteration] = ""; //reemplazo de null.
                    } else {
                        columns[readIteration] = selectResult.getString(readIteration + 1);
                    }
                }
                rows.add(columns);
            }
            Object[][] result = new Object[rows.size()][colNumber];
            for (int x = 0; x < rows.size(); x++) {
                result[x] = rows.get(x);
            }
            resultado = result;
        } else {
            for (String[] value : values) {
                int fieldIndex = 1;
                for (String fieldValue : value) {
                    prepStatement.setString(fieldIndex, fieldValue);
                    fieldIndex++;
                }
                prepStatement.addBatch();
            }
            int[] res = prepStatement.executeBatch();
            this.closeConnection();
            resultado = new Object[res.length][1];
            for (int x = 0; x < res.length; x++) {
                resultado[x][0] = res[x];
            }
        }

        return resultado;
    }

    protected Object[][] executeStatement(String sqlStatement) throws SQLException {
        int colNumber = 0;
        Object[][] resultado = new Object[0][0];
        //sqlStatement = escapeField(sqlStatement);
        this.openConnection();
        Statement statement = database.createStatement();
        if (sqlStatement.toLowerCase().startsWith("select")) {
            ResultSet selectResult = statement.executeQuery(sqlStatement);
            colNumber = selectResult.getMetaData().getColumnCount();
            Vector<String[]> rows = new Vector(); //interfaz String[] para ahorrar pasos.
            while (selectResult.next()) {
                String[] columns = new String[colNumber];
                for (int readIteration = 0; readIteration < colNumber; readIteration++) {
                    if (selectResult.getString(readIteration + 1) == null) {
                        columns[readIteration] = ""; //reemplazo de null.
                    } else {
                        columns[readIteration] = selectResult.getString(readIteration + 1);
                    }
                }
                rows.add(columns);
            }
            Object[][] result = new Object[rows.size()][colNumber];
            for (int x = 0; x < rows.size(); x++) {
                result[x] = rows.get(x);
            }
            resultado = result;
        } else {
            int resultNumber = statement.executeUpdate(sqlStatement);
            Object[][] result = new Object[1][1];
            result[0][0] = resultNumber;
            resultado = result;
        }

        this.closeConnection();

        return resultado;
    }
}
